package dao;

import model.Conn;
import model.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RecommendDao {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Conn cn = new Conn();

    /**
     * 获取类型商品的数量通过type
     *
     * @param typeID type字段值
     * @return int 总数
     * @throws SQLException
     */
    public int getCountById(int typeID) throws SQLException {
        String sql = "select count(*) ass from recommend where type=?";

        ps = cn.getConn().prepareStatement(sql);
        ps.setInt(1, typeID);
        rs = ps.executeQuery();
        rs.next();
        int count = rs.getInt("ass");
        Conn.ClossAll(conn, ps, rs);
        return count;
    }

    /**
     * 获取指定页数推荐类别商品通过type
     *
     * @param typeID     type
     * @param pageNumber 第几页
     * @param pageSize   一页的数量
     * @return List<Goods>
     * @throws SQLException
     */
    public List<Goods> selectGoodsByTypeID(int typeID, int pageNumber, int pageSize) throws SQLException {
        List<Goods> goodsList = new ArrayList<Goods>();
        String sql = "select * from recommend,goods where recommend.type=? and recommend.goods_id=goods.id limit ?,?";

        ps = cn.getConn().prepareStatement(sql);
        ps.setInt(1, typeID);
        ps.setInt(2, (pageNumber - 1) * pageSize);
        ps.setInt(3, pageSize);
        rs = ps.executeQuery();

        while (rs.next()) {
            Goods good = new Goods();
            good.setId(rs.getInt("goods.id"));
            good.setCover(rs.getString("cover"));
            good.setName(rs.getString("name"));
            good.setPrice(rs.getFloat("price"));
            goodsList.add(good);
        }
        Conn.ClossAll(conn, ps, rs);
        return goodsList;
    }

    /**
     * 判断type在不在recommend表中
     *
     * @param goodsId 商品id
     * @param type    type值
     * @return boolean
     * @throws SQLException
     */
    public boolean isRecommend(int goodsId, int type) throws SQLException {
        String sql = "SELECT * FROM recommend WHERE type = ? and goods_id = ?";
        conn = cn.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, type);
        ps.setInt(2, goodsId);
        rs = ps.executeQuery();
        boolean flag = rs.next();
        Conn.ClossAll(conn, ps, rs);
        return flag;
    }

    /**
     * 添加一个推荐类别
     *
     * @param type    类别码 1 条幅 2 热销 3新品
     * @param goodsId 商品id
     * @return boolean
     * @throws SQLException
     */
    public boolean addRecommend(int type, int goodsId) throws SQLException {
        String sql = "INSERT INTO `cakeshop`.`recommend` (`type`, `goods_id`) VALUES (?, ?)";
        conn = cn.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, type);
        ps.setInt(2, goodsId);
        int flag = ps.executeUpdate();
        Conn.ClossAll(conn, ps, rs);
        return flag > 0;
    }

    /**
     * 删除一个推荐类别通过type 和商品id
     *
     * @param type    type
     * @param goodsId 商品id
     * @return boolean
     * @throws SQLException
     */
    public boolean deleteRecommend(int type, int goodsId) throws SQLException {
        String sql = "DELETE FROM `cakeshop`.`recommend` WHERE `type` = ? AND `goods_id` = ?";
        conn = cn.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, type);
        ps.setInt(2, goodsId);
        int flag = ps.executeUpdate();
        Conn.ClossAll(conn, ps, rs);
        return flag > 0;
    }
}
